{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2a13a8d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "import sqlalchemy\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import tushare as ts\n",
    "from datetime import datetime\n",
    "from tqdm.notebook import tqdm\n",
    "from utils import update_financial_data\n",
    "from utils import update_dailybasic_data\n",
    "from utils import update_dailykline_data\n",
    "from utils import update_dailyadj_data\n",
    "from utils import update_hkhold_data\n",
    "\n",
    "from sqlalchemy import text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "24f98cee",
   "metadata": {},
   "outputs": [],
   "source": [
    "# table = 'hk_hold'\n",
    "# sql = text(\"DROP TABLE IF EXISTS {}\".format(table))\n",
    "# engine.execute(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3337171",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d7cc116e",
   "metadata": {},
   "outputs": [],
   "source": [
    "folder_path = 'data/'\n",
    "\n",
    "file_path = f'sqlite:////{folder_path}'\n",
    "\n",
    "token = 'd942e6ff0e981f76aaa544f84405583e0c2129a8c82213637835a099'\n",
    "pro = ts.pro_api(token)\n",
    "\n",
    "folder = 'data/'\n",
    "start_date = '20000101'\n",
    "\n",
    "today = datetime.strftime(datetime.now(),'%Y%m%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33ac5174",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1c5e4f0",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "2de95d6f",
   "metadata": {},
   "source": [
    "需要遍历的市场数据时间"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "88bfefca",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_calander = pro.trade_cal(exchange='SSE',start_date=start_date,end_date=today)\n",
    "df_calander = df_calander[df_calander['is_open']==1].reset_index(drop=True)\n",
    "cal_dates = list(df_calander['cal_date'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea21c6f2",
   "metadata": {},
   "source": [
    "需要遍历的财报数据时间"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8ccd3e0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "seasons = ['0331','0630','0930','1231']\n",
    "start_year = start_date[:4]\n",
    "end_year = '2099'\n",
    "end_dates = []\n",
    "for year in range(int(start_year),int(end_year)+1):\n",
    "    for season in seasons:\n",
    "        end_date = str(year) + season\n",
    "        end_dates.append(end_date)\n",
    "\n",
    "end_dates = list(filter(lambda x:x<today,end_dates))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "019b549c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "b6a54d79",
   "metadata": {},
   "source": [
    "更新财务数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "454331e5",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2021-11-23 16:03:06.664 | INFO     | utils:update_financial_data:172 - 需要更新0个财报日的数据\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "7e5351585d78460ea0963872ca2249af",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "0it [00:00, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2021-11-23 16:03:06.994 | INFO     | utils:update_financial_data:172 - 需要更新17个财报日的数据\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d9bcc3a358f645c0abefbd2b8bcc9a9f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "  0%|          | 0/17 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2021-11-23 16:03:12.822 | INFO     | utils:update_financial_data:172 - 需要更新0个财报日的数据\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a91a7dfbb4394bf5bcf0d841d7f85692",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "0it [00:00, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "database_name = 'financial_data.db'\n",
    "engine = create_engine(file_path+database_name)\n",
    "\n",
    "update_financial_data('利润表',end_dates,engine)\n",
    "update_financial_data('现金流量表',end_dates,engine)\n",
    "update_financial_data('资产负债表',end_dates,engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6004419",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "d9984a44",
   "metadata": {},
   "source": [
    "更新日频市场市场指标数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "cfb28255",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2021-11-23 16:03:27.044 | INFO     | utils:update_dailybasic_data:106 - 需要更新1个交易日的数据\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "34d11af3b8f647a8b00253eaf9e63862",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "  0%|          | 0/1 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "database_name='dailybasic.db'\n",
    "engine = create_engine(file_path+database_name)\n",
    "table_name = 'dailybasic'\n",
    "        \n",
    "update_dailybasic_data(table_name,cal_dates,engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "384d8b3d",
   "metadata": {},
   "source": [
    "更新原始K线数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "56074aca",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2021-11-23 16:03:52.213 | INFO     | utils:update_dailykline_data:120 - 需要更新1个交易日的数据\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d93362a020b149e28416d3de9ba8e517",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "  0%|          | 0/1 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "database_name='daily_kline.db'\n",
    "engine = create_engine(file_path+database_name)\n",
    "table_name = 'daily_kline'\n",
    "update_dailykline_data(table_name,cal_dates,engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77ee5f67",
   "metadata": {},
   "source": [
    "更新复权因子数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "91fddc5f",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2021-11-23 16:04:04.354 | INFO     | utils:update_dailyadj_data:134 - 需要更新0个交易日的数据\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "9f472b71ae914328b47d4395f5a0d0fc",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "0it [00:00, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "database_name='daily_adj.db'\n",
    "engine = create_engine(file_path+database_name)\n",
    "table_name = 'daily_adj'\n",
    "update_dailyadj_data(table_name,cal_dates,engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f500b01",
   "metadata": {},
   "source": [
    "更新沪深股通持股明细"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dbd2f5bd",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2021-11-23 16:04:04.404 | INFO     | utils:update_hkhold_data:148 - 需要更新5304个交易日的数据\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "2944014ead794262b36081f74a05f278",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "  0%|          | 0/5304 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "database_name='dailybasic.db'\n",
    "engine = create_engine(file_path+database_name)\n",
    "table_name = 'hk_hold'\n",
    "update_hkhold_data(table_name,cal_dates,engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "9abb7171",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.15"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "60/400"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc628fea",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "07864c62",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e90b638c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "fc998e4d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'tables': FacadeDict({}),\n",
       " 'schema': None,\n",
       " 'naming_convention': immutabledict({'ix': 'ix_%(column_0_label)s'}),\n",
       " '_schemas': set(),\n",
       " '_sequences': {},\n",
       " '_fk_memos': defaultdict(list, {}),\n",
       " '_bind': None}"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "database_name='dailybasic.db'\n",
    "engine = create_engine(file_path+database_name)\n",
    "Base = declarative_base()\n",
    "vars(Base.metadata)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9ab980ae",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ab155ea3",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6091bf49",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "abb69b52",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>ts_code</th>\n",
       "      <th>trade_date</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>pre_close</th>\n",
       "      <th>change</th>\n",
       "      <th>pct_chg</th>\n",
       "      <th>vol</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>000001.SZ</td>\n",
       "      <td>20211103</td>\n",
       "      <td>18.10</td>\n",
       "      <td>18.24</td>\n",
       "      <td>17.85</td>\n",
       "      <td>18.03</td>\n",
       "      <td>18.18</td>\n",
       "      <td>-0.15</td>\n",
       "      <td>-0.8251</td>\n",
       "      <td>1114972.04</td>\n",
       "      <td>2009325.065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>000002.SZ</td>\n",
       "      <td>20211103</td>\n",
       "      <td>17.70</td>\n",
       "      <td>18.09</td>\n",
       "      <td>17.56</td>\n",
       "      <td>18.05</td>\n",
       "      <td>17.64</td>\n",
       "      <td>0.41</td>\n",
       "      <td>2.3243</td>\n",
       "      <td>917347.10</td>\n",
       "      <td>1647494.236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>000004.SZ</td>\n",
       "      <td>20211103</td>\n",
       "      <td>16.34</td>\n",
       "      <td>16.91</td>\n",
       "      <td>16.34</td>\n",
       "      <td>16.73</td>\n",
       "      <td>16.39</td>\n",
       "      <td>0.34</td>\n",
       "      <td>2.0744</td>\n",
       "      <td>17822.11</td>\n",
       "      <td>29729.019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>000005.SZ</td>\n",
       "      <td>20211103</td>\n",
       "      <td>2.06</td>\n",
       "      <td>2.10</td>\n",
       "      <td>2.05</td>\n",
       "      <td>2.09</td>\n",
       "      <td>2.07</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.9662</td>\n",
       "      <td>73529.67</td>\n",
       "      <td>15255.666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>000006.SZ</td>\n",
       "      <td>20211103</td>\n",
       "      <td>4.08</td>\n",
       "      <td>4.13</td>\n",
       "      <td>4.06</td>\n",
       "      <td>4.12</td>\n",
       "      <td>4.08</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.9804</td>\n",
       "      <td>32980.25</td>\n",
       "      <td>13518.242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4519</th>\n",
       "      <td>4519</td>\n",
       "      <td>688799.SH</td>\n",
       "      <td>20211103</td>\n",
       "      <td>36.30</td>\n",
       "      <td>36.70</td>\n",
       "      <td>35.95</td>\n",
       "      <td>36.19</td>\n",
       "      <td>35.99</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.5557</td>\n",
       "      <td>2062.14</td>\n",
       "      <td>7483.386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4520</th>\n",
       "      <td>4520</td>\n",
       "      <td>688800.SH</td>\n",
       "      <td>20211103</td>\n",
       "      <td>92.34</td>\n",
       "      <td>94.65</td>\n",
       "      <td>88.99</td>\n",
       "      <td>90.20</td>\n",
       "      <td>89.47</td>\n",
       "      <td>0.73</td>\n",
       "      <td>0.8159</td>\n",
       "      <td>9991.37</td>\n",
       "      <td>91692.534</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4521</th>\n",
       "      <td>4521</td>\n",
       "      <td>688819.SH</td>\n",
       "      <td>20211103</td>\n",
       "      <td>42.20</td>\n",
       "      <td>42.67</td>\n",
       "      <td>41.80</td>\n",
       "      <td>42.21</td>\n",
       "      <td>42.17</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.0949</td>\n",
       "      <td>17571.72</td>\n",
       "      <td>74242.696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4522</th>\n",
       "      <td>4522</td>\n",
       "      <td>688981.SH</td>\n",
       "      <td>20211103</td>\n",
       "      <td>55.99</td>\n",
       "      <td>57.17</td>\n",
       "      <td>55.48</td>\n",
       "      <td>56.99</td>\n",
       "      <td>55.99</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.7860</td>\n",
       "      <td>404008.38</td>\n",
       "      <td>2277206.463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4523</th>\n",
       "      <td>4523</td>\n",
       "      <td>689009.SH</td>\n",
       "      <td>20211103</td>\n",
       "      <td>65.99</td>\n",
       "      <td>65.99</td>\n",
       "      <td>63.75</td>\n",
       "      <td>64.41</td>\n",
       "      <td>65.25</td>\n",
       "      <td>-0.84</td>\n",
       "      <td>-1.2874</td>\n",
       "      <td>18036.91</td>\n",
       "      <td>116281.415</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4524 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      index    ts_code trade_date   open   high    low  close  pre_close  \\\n",
       "0         0  000001.SZ   20211103  18.10  18.24  17.85  18.03      18.18   \n",
       "1         1  000002.SZ   20211103  17.70  18.09  17.56  18.05      17.64   \n",
       "2         2  000004.SZ   20211103  16.34  16.91  16.34  16.73      16.39   \n",
       "3         3  000005.SZ   20211103   2.06   2.10   2.05   2.09       2.07   \n",
       "4         4  000006.SZ   20211103   4.08   4.13   4.06   4.12       4.08   \n",
       "...     ...        ...        ...    ...    ...    ...    ...        ...   \n",
       "4519   4519  688799.SH   20211103  36.30  36.70  35.95  36.19      35.99   \n",
       "4520   4520  688800.SH   20211103  92.34  94.65  88.99  90.20      89.47   \n",
       "4521   4521  688819.SH   20211103  42.20  42.67  41.80  42.21      42.17   \n",
       "4522   4522  688981.SH   20211103  55.99  57.17  55.48  56.99      55.99   \n",
       "4523   4523  689009.SH   20211103  65.99  65.99  63.75  64.41      65.25   \n",
       "\n",
       "      change  pct_chg         vol       amount  \n",
       "0      -0.15  -0.8251  1114972.04  2009325.065  \n",
       "1       0.41   2.3243   917347.10  1647494.236  \n",
       "2       0.34   2.0744    17822.11    29729.019  \n",
       "3       0.02   0.9662    73529.67    15255.666  \n",
       "4       0.04   0.9804    32980.25    13518.242  \n",
       "...      ...      ...         ...          ...  \n",
       "4519    0.20   0.5557     2062.14     7483.386  \n",
       "4520    0.73   0.8159     9991.37    91692.534  \n",
       "4521    0.04   0.0949    17571.72    74242.696  \n",
       "4522    1.00   1.7860   404008.38  2277206.463  \n",
       "4523   -0.84  -1.2874    18036.91   116281.415  \n",
       "\n",
       "[4524 rows x 12 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(f\"select * from 'daily_kline' where trade_date='20211103'\",engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2589fffe",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
